﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.IO;

public partial class A_MaintainPage : System.Web.UI.Page
{
    public static DataTable ta = new DataTable();//用于存储从数据库中取出来的表格，可以在多个函数中使用，所以使用static
    public static DataView vi = new DataView();  //用于数据绑定
    public static string PageState = "student";
    protected void Page_Load(object sender, EventArgs e)
    {
        btnFirst.Text = "最首页";    //分页处理
        btnPrev.Text = "前一页";
        btnNext.Text = "下一页";
        btnLast.Text = "最后页";

        if (!IsPostBack)
        {
            PageState = "student";
            Panel1.Visible = true;
            Panel2.Visible = false;
            Panel3.Visible = false;            
            ShowStudent();
            string sql = "select * from STU_INFO where STU_INFO.state='1';";
            ExecuteSQL(sql);

        }
    }
    void ExecuteSQL(string sql)
    {
        //string sql = "select courseid,COURSE_INFO.name as coursename,studentid,STU_INFO.name as studentname,STU_INFO.class as class,mark,SEM_INFO.id as semesterid,SEM_INFO.name as semester from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.semesterid=SEM_INFO.id and CLASS_INFO.state='1' order by STU_INFO.id ;";
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        ta.Columns.Clear();
        ta.Rows.Clear();
        da.Fill(ta);
        vi = new DataView(ta);
        dg1.DataSource = vi;
        dg1.DataBind();
        myConn.Close();
    }
   
    public void MyDataGrid_Edit(Object sender, DataGridCommandEventArgs e)//datagrid中点击编辑edit时执行函数
    {

        vi = new DataView(ta);                                          //从ta中获取数据源用于绑定

        dg1.EditItemIndex = e.Item.ItemIndex;
        dg1.DataSource = vi;
        dg1.DataBind();

    }

    public void MyDataGrid_Cancel(Object sender, DataGridCommandEventArgs e) //处于可编辑状态时，点击cancel执行函数
    {

        dg1.EditItemIndex = -1;
        vi = new DataView(ta);
        dg1.DataSource = vi;
        dg1.DataBind();

    }

 
    public void MyDataGrid_Update(Object sender, DataGridCommandEventArgs e) //处于可编辑状态时，修改表格数据后，点击update执行函数
    {

        int index = dg1.CurrentPageIndex * dg1.PageSize + e.Item.ItemIndex;    //获取修改行的索引号(需要加上分页计算)
        string sql = "";
        if (PageState == "student")
        {
            TextBox idText = (TextBox)e.Item.Cells[2].Controls[0];          //此处获取表格中修改行的所有数据，以便下面程序删除该行，重新增加新行
            TextBox nameText = (TextBox)e.Item.Cells[3].Controls[0];
            TextBox genderText = (TextBox)e.Item.Cells[4].Controls[0];
            TextBox collegeidText = (TextBox)e.Item.Cells[5].Controls[0];
            TextBox specialityText = (TextBox)e.Item.Cells[6].Controls[0];
            TextBox classText = (TextBox)e.Item.Cells[7].Controls[0];
            TextBox phoneText = (TextBox)e.Item.Cells[8].Controls[0];
            TextBox emailText = (TextBox)e.Item.Cells[9].Controls[0];

            string id = idText.Text;
            string name = nameText.Text;
            string gender = genderText.Text;
            string collegeid = collegeidText.Text;
            string speciality = specialityText.Text;
            string Class= classText.Text;
            string phone = phoneText.Text;
            string email = emailText.Text;



            DataRow dr = ta.NewRow();                  //创建一个ta类型中的新行

            dr[0] = id; dr[1] = name; dr[2] = gender; dr[3] = collegeid; dr[4] = speciality; dr[5] = Class; dr[6] = phone; dr[7] = email; dr[8] = "1";
            string studentid = (string)ta.Rows[index][0];
            sql = "update STU_INFO set id='" +id + "',name='" + name + "',gender='" + gender + "',collegeid='" + collegeid + "',speciality='" + speciality + "',class='" + Class + "',phone='" + phone + "',email='" + email + "' where id='" + studentid + "';";
       


            ta.Rows.RemoveAt(index);              //删除ta中的旧行，添加新行
            ta.Rows.InsertAt(dr, index);
        }

        else if (PageState == "teacher")
        {
            TextBox idText = (TextBox)e.Item.Cells[2].Controls[0];          //此处获取表格中修改行的所有数据，以便下面程序删除该行，重新增加新行
            TextBox nameText = (TextBox)e.Item.Cells[3].Controls[0];
            TextBox genderText = (TextBox)e.Item.Cells[4].Controls[0];
            TextBox rankText = (TextBox)e.Item.Cells[5].Controls[0];
            TextBox collegeidText = (TextBox)e.Item.Cells[6].Controls[0];
            TextBox phoneText = (TextBox)e.Item.Cells[7].Controls[0];
            TextBox emailText = (TextBox)e.Item.Cells[8].Controls[0];

            string id = idText.Text;
            string name = nameText.Text;
            string gender = genderText.Text;
            string rank = rankText.Text;
            string collegeid = collegeidText.Text;
            string phone = phoneText.Text;
            string email = emailText.Text;



            DataRow dr = ta.NewRow();                  //创建一个ta类型中的新行

            dr[0] = id; dr[1] = name; dr[2] = gender; dr[3] = rank; dr[4] = collegeid; dr[5] = phone; dr[6] = email; dr[7] = "1";
            string teacherid = (string)ta.Rows[index][0];
            sql = "update TEA_INFO set id='" + id + "',name='" + name + "',gender='" + gender + "',rank='"+rank+"',collegeid='" + collegeid + "',phone='" + phone + "',email='" + email + "' where id='" + teacherid + "';";



            ta.Rows.RemoveAt(index);              //删除ta中的旧行，添加新行
            ta.Rows.InsertAt(dr, index);
        }
        else if (PageState == "course")
        {
            TextBox idText = (TextBox)e.Item.Cells[2].Controls[0];          //此处获取表格中修改行的所有数据，以便下面程序删除该行，重新增加新行
            TextBox nameText = (TextBox)e.Item.Cells[3].Controls[0];
            TextBox collegeidText = (TextBox)e.Item.Cells[4].Controls[0];
            TextBox teacheridText = (TextBox)e.Item.Cells[5].Controls[0];
            TextBox timeText = (TextBox)e.Item.Cells[6].Controls[0];
            TextBox roomText = (TextBox)e.Item.Cells[7].Controls[0];
            TextBox creditText = (TextBox)e.Item.Cells[8].Controls[0];

            string id = idText.Text;
            string name = nameText.Text;
            string collegeid = collegeidText.Text;
            string teacherid = teacheridText.Text;
            string time = timeText.Text;
            string room = roomText.Text;
            string credit = creditText.Text;



            DataRow dr = ta.NewRow();                  //创建一个ta类型中的新行

            dr[0] = id; dr[1] = name; dr[2] = collegeid; dr[3] = teacherid; dr[4] = time; dr[5] = room; dr[6] = credit; dr[7] = "1";
            string courseid = (string)ta.Rows[index][0];
            sql = "update COURSE_INFO set id='" + id + "',name='" + name + "',collegeid='" + collegeid + "',teacherid='" + teacherid + "',time='" + time + "',room='" + room + "',credit='" + credit + "' where id='" + courseid + "';";



            ta.Rows.RemoveAt(index);              //删除ta中的旧行，添加新行
            ta.Rows.InsertAt(dr, index);
        }

        vi = new DataView(ta);               //重新数据绑定vi
        dg1.EditItemIndex = -1;
        dg1.DataSource = vi;
        dg1.DataBind();


        //对数据库操作，更新数据，此处会用到上面保存的studentid
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();
        cmd.ExecuteNonQuery();

        myConn.Close();




    }
  
    public void MyDataGrid_Delete(Object sender, DataGridCommandEventArgs e)  //datagrid中删除行时执行函数
    {

        int index = dg1.CurrentPageIndex * dg1.PageSize + e.Item.ItemIndex;             //获取被删除行的索引
        string sql = "";
        if (PageState == "student")
        { 
           string studentid = e.Item.Cells[2].Text;
           sql = "update STU_INFO set state='0' where id='" + studentid + "';";
           Execute(sql);
           sql = "update CLASS_INFO set state='0' where studentid='"+studentid+"';";
           Execute(sql);
        }
        else if (PageState == "teacher")
        { 
           string teacherid = e.Item.Cells[2].Text;
           sql = "update TEA_INFO set state='0' where id='" + teacherid + "';";
           Execute(sql);
           sql = "update CLASS_INFO set state='0' where courseid in(select id from COURSE_INFO where teacherid='"+teacherid+"');";
           Execute(sql);
        }
        else if (PageState == "course")
        {
            string courseid = e.Item.Cells[2].Text;               //保存主键id
            sql = "update COURSE_INFO set state='0' where id='" + courseid + "';";
            Execute(sql);
            sql = "update CLASS_INFO set state='0' where courseid ='" + courseid + "';";
            Execute(sql);
        }
        
        
        

        ta.Rows.RemoveAt(index);                  //删除旧行

        vi = new DataView(ta);                    //重新绑定
        dg1.EditItemIndex = -1;
        dg1.DataSource = vi;
        dg1.DataBind();

        /*
        //更新数据库数据
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();
        cmd.ExecuteNonQuery();

        myConn.Close();
         */
    }

    private void ShowStats()  //用于分页处理
    {
        lblCurrentIndex.Text = "第 " + (dg1.CurrentPageIndex + 1).ToString() + " 页";
        lblPageCount.Text = "总共 " + dg1.PageCount.ToString() + " 页";
    }

    public void PagerButtonClick(object sender, EventArgs e) //用于分页处理
    {
        string arg = ((LinkButton)sender).CommandArgument.ToString();
        switch (arg)
        {
            case "next":
                if (dg1.CurrentPageIndex < (dg1.PageCount - 1))
                {
                    dg1.CurrentPageIndex += 1;
                }
                break;
            case "prev":
                if (dg1.CurrentPageIndex > 0)
                {
                    dg1.CurrentPageIndex -= 1;
                }
                break;
            case "last":
                dg1.CurrentPageIndex = (dg1.PageCount - 1);
                break;
            default:
                dg1.CurrentPageIndex = System.Convert.ToInt32(arg);
                break;
        }
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();
    }

    public void MyDataGrid_Page(Object source, DataGridPageChangedEventArgs e)  //用于分页处理
    {
        int startIndex;
        startIndex = dg1.CurrentPageIndex * dg1.PageSize;
        dg1.CurrentPageIndex = e.NewPageIndex;
        //BindGrid();
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();

    }
    string ReaderValue(string sql)
    {
        string value = "";
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();

        SqlDataReader myReader = cmd.ExecuteReader();
        int i = 0;
        while (myReader.Read())
        {
            value = myReader[0].ToString().Trim();
            i++;
        }
        myReader.Close();
        myConn.Close();
        return value;
    }
    void Execute(string sql)
    {
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();
        cmd.ExecuteNonQuery();
        myConn.Close();
    }
    void ShowStudent()
    {
        TextBox1.Text="";
        TextBox2.Text="";
        TextBox3.Text="";
        TextBox4.Text="";
        TextBox5.Text="";
        TextBox6.Text="";

        RadioButton1.Checked =true;
        DL1.Text="请选择班级";

    }
    void ShowTeacher()
    {
        TextBox7.Text="";
        TextBox8.Text="";
        TextBox9.Text="";
        TextBox10.Text="";
        TextBox11.Text="";

        RadioButton3.Checked =true;

        DL2.Text="请选择职称";
    }

    void ShowCourse()
    {
        TextBox19.Text="";
        TextBox20.Text="";
        TextBox21.Text="";

        TextBox22.Text="";
        TextBox23.Text="";
        TextBox24.Text="";
        TextBox25.Text="";
                
    }

    protected void LinkButton1_Click(object sender, EventArgs e)//首页
    {

    }
    protected void LinkButton2_Click(object sender, EventArgs e)//学生基本信息
    {
        PageState = "student";
        Panel1.Visible = true;
        Panel2.Visible = false;
        Panel3.Visible = false;
        ShowStudent();
        string sql = "select * from STU_INFO where STU_INFO.state='1';";
        ExecuteSQL(sql);
    }
    protected void LinkButton3_Click(object sender, EventArgs e)//教师基本信息
    {
        PageState = "teacher";
        Panel1.Visible = false;
        Panel2.Visible = true;
        Panel3.Visible = false;
        ShowTeacher();
        string sql = "select * from TEA_INFO where TEA_INFO.state='1';";
        ExecuteSQL(sql);
    }
    protected void LinkButton4_Click(object sender, EventArgs e)//课程基本信息
    {
        PageState = "course";
        Panel1.Visible = false;
        Panel2.Visible = false;
        Panel3.Visible = true;
        ShowCourse();
        string sql = "select * from COURSE_INFO where COURSE_INFO.state='1';";
        ExecuteSQL(sql);
    }

    protected void RadioButton1_CheckedChanged(object sender, EventArgs e)
    {
        RadioButton1.Checked=true;
        RadioButton2.Checked = false;
    }
    protected void RadioButton2_CheckedChanged(object sender, EventArgs e)
    {
        RadioButton1.Checked=false;
        RadioButton2.Checked=true;
    }
    protected void RadioButton3_CheckedChanged(object sender, EventArgs e)
    {
        RadioButton3.Checked = true;
        RadioButton4.Checked = false;
    }
    protected void RadioButton4_CheckedChanged(object sender, EventArgs e)
    {
        RadioButton3.Checked = false;
        RadioButton4.Checked = true;
    }
    protected void Button1_Click(object sender, EventArgs e)//学生信息提交
    {
        if (TextBox1.Text == "" || TextBox2.Text == "" || TextBox3.Text == "" || TextBox4.Text == "" || TextBox5.Text == "" || TextBox6.Text == "")
        {
            Alert("请确认信息是否填写完全");
        }
        else
        {
            if (DL1.Text == "请选择班级")
            {
                Alert("请选择班级");
            }
            else
            {
                string id = TextBox1.Text;
                string name = TextBox2.Text;
                string collegeid = TextBox3.Text;
                string speciality = TextBox4.Text;
                string phone = TextBox5.Text;
                string email = TextBox6.Text;
                string gender = "";
                if (RadioButton1.Checked == true)
                {
                    gender = "男";
                }
                else if(RadioButton2.Checked==true)
                {
                    gender = "女";
                }
                string Class = DL1.Text;

                try
                {
                    string sql = "insert into STU_INFO values('" + id + "','" + name + "','" + gender + "','" + collegeid + "','" + speciality + "','" + Class + "','" + phone + "','" + email + "','1');";
                    Execute(sql);//缺乏出错处理
                    sql = "select * from STU_INFO where STU_INFO.state='1';";
                    ExecuteSQL(sql);
                    Alert("提交成功");
                }
                catch
                { 
                  
                }
            }
        }
    }
    protected void Button2_Click(object sender, EventArgs e)//教师信息提交
    {
        if (TextBox7.Text == "" || TextBox8.Text == "" || TextBox9.Text == "" || TextBox10.Text == "" || TextBox11.Text == "")
        {
            Alert("请确认信息是否填写完全");
        }
        else
        {
            if (DL2.Text == "请选择职称")
            {
                Alert("请选择职称");
            }
            else
            {
                string id = TextBox7.Text;
                string name = TextBox8.Text;
                string collegeid = TextBox9.Text;
                string phone = TextBox10.Text;
                string email = TextBox11.Text;
                string gender = "";
                if (RadioButton3.Checked == true)
                {
                    gender = "男";
                }
                else if (RadioButton4.Checked == true)
                {
                    gender = "女";
                }
                string rank = DL2.Text;

                try
                {
                    string sql = "insert into TEA_INFO values('" + id + "','" + name + "','" + gender + "','" + rank + "','" + collegeid + "','" + phone + "','" + email + "','1');";
                    Execute(sql);//缺乏出错处理
                    sql = "select * from TEA_INFO where TEA_INFO.state='1';";
                    ExecuteSQL(sql);
                    Alert("提交成功");
                }
                catch
                { 
                 
                }
            }
        }
    }
    protected void Button3_Click(object sender, EventArgs e)//课程信息提交
    {
        if (TextBox19.Text == "" || TextBox20.Text == "" || TextBox21.Text == "" || TextBox22.Text == "" || TextBox23.Text == "" || TextBox24.Text == "" || TextBox25.Text == "")
        {
            Alert("请确认信息是否填写完全");
        }
        else
        {
                string id = TextBox19.Text;
                string name = TextBox20.Text;
                string collegeid = TextBox21.Text;

                string teacherid = TextBox22.Text;
                string time = TextBox23.Text;
                string room = TextBox24.Text;
                string credit = TextBox25.Text;


                try
                {
                    string sql = "insert into COURSE_INFO values('" + id + "','" + name + "','" + collegeid + "','" + teacherid + "','" + time + "','" + room + "','" + credit + "','1');";
                    Execute(sql);//缺乏出错处理
                    sql = "select * from COURSE_INFO where COURSE_INFO.state='1';";
                    ExecuteSQL(sql);
                    Alert("提交成功");
                }
                catch
                { 
                
                }
        }
    }

    void Alert(string message)//用于测试，无实际意义
    {
        Response.Write("<script>alert('" + message + "')</script>");
    }
}
